#! -*- coding: utf-8 -*-
"""
mssql
"""
import time
import logging
import pymssql


LOG = logging.getLogger("server.mssql")


class MSSQL(object):
    """
    MSSQL
    """

    def __init__(self, host, port, user, pwd, db, charset="cp936"):
        self.host = host
        self.port = port
        self.user = user
        self.pwd = pwd
        self.dateb = db
        self.charset = charset

        self.conn = None
        self.cur = None

    def __del__(self):
        self.close()

    def close(self):
        """
        关闭连接
        """
        if self.conn:
            self.conn.close()
            self.conn = None
            self.cur = None

    def _initconnect(self):
        """
        init connect
        """
        if not self.dateb:
            raise(ValueError, r"没有设置数据库信息")

        LOG.info("connect mssql %s %s %s %s", \
            self.host, self.port, self.user, self.dateb)
        self.conn = pymssql.connect(host=self.host, user=self.user, charset=self.charset,\
            password=self.pwd, database=self.dateb, port=self.port, login_timeout=30)
        self.cur = self.conn.cursor()

        if not self.cur:
            raise(ValueError, r"连接数据库失败")

    def _getconnect(self):
        """
        得到连接信息
        返回: conn.cursor()
        """
        if not self.cur:
            self._initconnect()
        return self.cur

    def exec_query(self, sql):
        """
        exec_query
        """
        try:
            return self._exec_query(sql)
        except pymssql.OperationalError, e:
            LOG.warn("reconnect mssql after 0.1s")
            time.sleep(0.1)
            self.close()
            return self._exec_query(sql)

    def _exec_query(self, sql):
        """
        执行查询语句
        返回的是一个包含tuple的list，list的元素是记录行，tuple的元素是每行记录的字段

        调用示例：
                ms = MSSQL(host="localhost",user="sa",pwd="123456",db="PythonWeiboStatistics")
                resList = ms.ExecQuery("SELECT id,NickName FROM WeiBoUser")
                for (id,NickName) in resList:
                    print str(id),NickName
        """
        cur = self._getconnect()
        LOG.info("db %s exec %s", self.dateb, sql)
        cur.execute(sql)
        reslist = cur.fetchall()
        return reslist

    def exec_nonquery(self, sql):
        """
        exec_nonquery
        """
        try:
            return self._exec_nonquery(sql)
        except pymssql.OperationalError, e:
            LOG.warn("reconnect mssql after 0.1s")
            time.sleep(0.1)
            self.close()
            return self._exec_nonquery(sql)

    def _exec_nonquery(self, sql):
        """
        执行非查询语句
        """
        cur = self._getconnect()
        LOG.info("db %s exec %s", self.dateb, sql)
        cur.execute(sql)
        self.conn.commit()
